package com.fhd.dao.Merchant;

import com.fhd.dao.BaseDao;
import com.fhd.pojo.Merchant;
import com.fhd.pojo.Recharge;
import com.mysql.cj.util.StringUtils;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Objects;

public class MerchantDaoImplement implements MerchantDao{
    // 通过账号得到商户
    @Override
    public Merchant getLoginMerchant(Connection conn, String userCode) {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Merchant merchant = new Merchant();

        try {
            if (conn != null) {
                // 编写sql语句,并把参数放进数组中
                String sql = "select * from merchant where user_code = ?";
                Object[] parameters = {userCode};
                rs = BaseDao.executeQuery(conn, pstmt, rs, sql, parameters);

                if(rs.next()) {
                    merchant.setId(rs.getInt("id"));
                    merchant.setUserCode(rs.getString("user_code"));
                    merchant.setUserPassword(rs.getString("user_password"));
                    merchant.setUserName(rs.getString("user_name"));
                    merchant.setRoleId(rs.getInt("role_id"));
                    merchant.setShopName(rs.getString("shop_name"));
                    merchant.setAddress(rs.getString("address"));
                    merchant.setPhone(rs.getString("phone"));
                    merchant.setKeyWord(rs.getString("key_word"));
                    merchant.setWorkTimeBegin(rs.getTime("work_time_begin"));
                    merchant.setWorkTimeEnd(rs.getTime("work_time_end"));
                    merchant.setOpenSituation(rs.getInt("open_situation"));
                    merchant.setApplySituation(rs.getInt("apply_situation"));
                    merchant.setMoney(rs.getInt("money"));
                    merchant.setSoldNumber(rs.getInt("sold_number"));
                    merchant.setCreateDate(rs.getDate("create_date"));
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            BaseDao.close(null,pstmt,rs);
        }
        return merchant;
    }

    // 通过id得到商户
    @Override
    public Merchant getMerchantById(Connection conn, int id) {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Merchant merchant = new Merchant();

        try {
            if (conn != null) {
                // 编写sql语句,并把参数放进数组中
                String sql = "select * from merchant where id = ?";
                Object[] parameters = {id};
                rs = BaseDao.executeQuery(conn, pstmt, rs, sql, parameters);

                if(rs.next()) {
                    merchant.setId(rs.getInt("id"));
                    merchant.setUserCode(rs.getString("user_code"));
                    merchant.setUserPassword(rs.getString("user_password"));
                    merchant.setUserName(rs.getString("user_name"));
                    merchant.setRoleId(rs.getInt("role_id"));
                    merchant.setShopName(rs.getString("shop_name"));
                    merchant.setAddress(rs.getString("address"));
                    merchant.setPhone(rs.getString("phone"));
                    merchant.setKeyWord(rs.getString("key_word"));
                    merchant.setWorkTimeBegin(rs.getTime("work_time_begin"));
                    merchant.setWorkTimeEnd(rs.getTime("work_time_end"));
                    merchant.setOpenSituation(rs.getInt("open_situation"));
                    merchant.setApplySituation(rs.getInt("apply_situation"));
                    merchant.setMoney(rs.getInt("money"));
                    merchant.setSoldNumber(rs.getInt("sold_number"));
                    merchant.setCreateDate(rs.getDate("create_date"));
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            BaseDao.close(null,pstmt,rs);
        }
        return merchant;
    }

    // 注册商户账号
    @Override
    public int signUpMerchant(Connection conn, String userCode, String userPassword, String userName, String shopName, String address, String phone, String keyWord, Time workTimeBegin, Time workTimeEnd) {
        PreparedStatement pstmt = null;
        int affectedRow = 0;
        try {
            if (conn != null) {
                // 编写sql语句,并把参数放进数组中
                String sql = "insert into merchant (user_code,user_password,user_name,shop_name,address,phone,key_word,work_time_begin,work_time_end,create_date) values (?,?,?,?,?,?,?,?,?,?)";
                // 加入创建时间
                Date date =  new Date();
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                date = sdf.parse(sdf.format(date));
                Object[] parameters = {userCode,userPassword,userName,shopName,address,phone,keyWord,workTimeBegin,workTimeEnd,date};
                affectedRow = BaseDao.executeUpdate(conn, pstmt,sql, parameters);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            BaseDao.close(null,pstmt,null);
        }
        return affectedRow;
    }

    // 得到所有的商户信息
    @Override
    public ArrayList<Merchant> getAllMerchant(Connection conn) {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<Merchant> merchants = new ArrayList<>();

        try {
            if (conn != null) {
                // 编写sql语句,并把参数放进数组中
                String sql = "select * from merchant";
                Object[] parameters = {};
                rs = BaseDao.executeQuery(conn, pstmt, rs, sql, parameters);

                while (rs.next()) {
                    Merchant merchant = new Merchant();
                    merchant.setId(rs.getInt("id"));
                    merchant.setUserCode(rs.getString("user_code"));
                    merchant.setUserPassword(rs.getString("user_password"));
                    merchant.setUserName(rs.getString("user_name"));
                    merchant.setRoleId(rs.getInt("role_id"));
                    merchant.setShopName(rs.getString("shop_name"));
                    merchant.setAddress(rs.getString("address"));
                    merchant.setPhone(rs.getString("phone"));
                    merchant.setKeyWord(rs.getString("key_word"));
                    merchant.setWorkTimeBegin(rs.getTime("work_time_begin"));
                    merchant.setWorkTimeEnd(rs.getTime("work_time_end"));
                    merchant.setOpenSituation(rs.getInt("open_situation"));
                    merchant.setApplySituation(rs.getInt("apply_situation"));
                    merchant.setMoney(rs.getInt("money"));
                    merchant.setSoldNumber(rs.getInt("sold_number"));
                    merchant.setCreateDate(rs.getDate("create_date"));
                    merchants.add(merchant);
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            BaseDao.close(null,pstmt,rs);
        }
        return merchants;
    }

    // 模糊查询商户
    @Override
    public ArrayList<Merchant> getSomeMerchant(Connection conn, String shopName) {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<Merchant> merchants = new ArrayList<>();

        try {
            if (conn != null) {
                // 编写sql语句,并把参数放进数组中
                String sql = "select * from merchant where shop_name like ?";
                Object[] parameters = {"%"+shopName+"%"};
                rs = BaseDao.executeQuery(conn, pstmt, rs, sql, parameters);

                while (rs.next()) {
                    Merchant merchant = new Merchant();
                    merchant.setId(rs.getInt("id"));
                    merchant.setUserCode(rs.getString("user_code"));
                    merchant.setUserPassword(rs.getString("user_password"));
                    merchant.setRoleId(rs.getInt("role_id"));
                    merchant.setShopName(rs.getString("shop_name"));
                    merchant.setAddress(rs.getString("address"));
                    merchant.setPhone(rs.getString("phone"));
                    merchant.setKeyWord(rs.getString("key_word"));
                    merchant.setWorkTimeBegin(rs.getTime("work_time_begin"));
                    merchant.setWorkTimeEnd(rs.getTime("work_time_end"));
                    merchant.setOpenSituation(rs.getInt("open_situation"));
                    merchant.setApplySituation(rs.getInt("apply_situation"));
                    merchant.setMoney(rs.getInt("money"));
                    merchant.setSoldNumber(rs.getInt("sold_number"));
                    merchant.setCreateDate(rs.getDate("create_date"));
                    merchants.add(merchant);
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            BaseDao.close(null,pstmt,rs);
        }
        return merchants;
    }

    // 改变申请状态
    @Override
    public int changeAppleSituation(Connection conn, int id, int situation) {
        PreparedStatement pstmt = null;
        int affectedRow = 0;
        try {
            if (conn != null) {
                // 编写sql语句,并把参数放进数组中
                String sql = "update merchant set apply_situation = ? where id = ?";
                Object[] parameters = {situation,id};
                affectedRow = BaseDao.executeUpdate(conn, pstmt,sql, parameters);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            BaseDao.close(null,pstmt,null);
        }
        return affectedRow;
    }

    // 更改商户信息
    @Override
    public int updateInformation(Connection conn, int id, String userCode, String userPassword, String userName, String shopName, String address, String phone, String keyWord, Time workTimeBegin, Time workTimeEnd, int openSituation) {
        PreparedStatement pstmt = null;
        int affectedRow = 0;
        ArrayList<Object> objects = new ArrayList<>();
        try {
            if (conn != null) {
                // 通过前端传来的数据拼接sql语句,并把参数先放进ArrayList中
                StringBuffer sql = new StringBuffer("update merchant set ");
                if (!StringUtils.isNullOrEmpty(userCode)) {
                    sql.append("user_code = ?,");
                    objects.add(userCode);
                }
                if (!StringUtils.isNullOrEmpty(userPassword)) {
                    sql.append("user_password = ?,");
                    objects.add(userPassword);
                }
                if (!StringUtils.isNullOrEmpty(userName)) {
                    sql.append("user_name = ?,");
                    objects.add(userName);
                }
                if (!StringUtils.isNullOrEmpty(shopName)) {
                    sql.append("shop_name = ?,");
                    objects.add(shopName);
                }
                if (!StringUtils.isNullOrEmpty(address)) {
                    sql.append("address = ?,");
                    objects.add(address);
                }
                if (!StringUtils.isNullOrEmpty(phone)) {
                    sql.append("phone = ?,");
                    objects.add(phone);
                }
                if (!StringUtils.isNullOrEmpty(keyWord)) {
                    sql.append("key_word = ?,");
                    objects.add(keyWord);
                }
                if (!StringUtils.isNullOrEmpty(shopName)) {
                    sql.append("shop_name = ?,");
                    objects.add(shopName);
                }
                if (workTimeBegin != null) {
                    sql.append("work_time_begin = ?,");
                    objects.add(workTimeBegin);
                }
                if (workTimeEnd != null) {
                    sql.append("work_time_end = ?,");
                    objects.add(workTimeEnd);
                }
                if (openSituation == 0 || openSituation == 1) {
                    sql.append("open_situation = ?,");
                    objects.add(openSituation);
                }
                sql.deleteCharAt(sql.lastIndexOf(","));
                sql.append(" where id = ?");
                objects.add(id);

                Object[] parameters = objects.toArray();
                affectedRow = BaseDao.executeUpdate(conn, pstmt,sql.toString(),parameters);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            BaseDao.close(null,pstmt,null);
        }
        return affectedRow;
    }

    // 排序所有商户
    @Override
    public ArrayList<Merchant> orderMerchant(Connection conn,String soldNumberOrder, String createDateOrder) {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<Merchant> merchants = new ArrayList<>();

        try {
            if (conn != null) {
                // 编写sql语句,并把参数放进数组中
                StringBuffer sql = new StringBuffer("select * from merchant order by ");
                if (!Objects.equals(soldNumberOrder,"null")) {
                    // 因为如果交给PreparedStatement拼接，会多一对单引号，最终出现 order by 'ASC'的效果，故在此处写死
                    if (Objects.equals(soldNumberOrder,"ASC")) {
                        sql.append(" sold_number ASC,");
                    } else {
                        sql.append(" sold_number DESC,");
                    }
                }

                if (!Objects.equals(createDateOrder,"null")) {
                    // 同理写死
                    if (Objects.equals(createDateOrder,"ASC")) {
                        sql.append(" create_date ASC,");
                    } else {
                        sql.append(" create_date DESC,");
                    }
                }

                sql.deleteCharAt(sql.lastIndexOf(","));
                Object[] parameters = {};
                rs = BaseDao.executeQuery(conn, pstmt, rs, sql.toString(), parameters);

                while (rs.next()) {
                    Merchant merchant = new Merchant();
                    merchant.setId(rs.getInt("id"));
                    merchant.setUserCode(rs.getString("user_code"));
                    merchant.setUserPassword(rs.getString("user_password"));
                    merchant.setUserName(rs.getString("user_name"));
                    merchant.setRoleId(rs.getInt("role_id"));
                    merchant.setShopName(rs.getString("shop_name"));
                    merchant.setAddress(rs.getString("address"));
                    merchant.setPhone(rs.getString("phone"));
                    merchant.setKeyWord(rs.getString("key_word"));
                    merchant.setWorkTimeBegin(rs.getTime("work_time_begin"));
                    merchant.setWorkTimeEnd(rs.getTime("work_time_end"));
                    merchant.setOpenSituation(rs.getInt("open_situation"));
                    merchant.setApplySituation(rs.getInt("apply_situation"));
                    merchant.setMoney(rs.getInt("money"));
                    merchant.setSoldNumber(rs.getInt("sold_number"));
                    merchant.setCreateDate(rs.getDate("create_date"));
                    merchants.add(merchant);
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            BaseDao.close(null,pstmt,rs);
        }
        return merchants;
    }

    //增加/减少收入
    @Override
    public int updateMoney(Connection conn, int id, int money) {
        PreparedStatement pstmt = null;
        int affectedRow = 0;
        try {
            if (conn != null) {
                // 编写sql语句,并把参数放进数组中
                String sql = "update merchant set money = money + ? where id = ?";
                Object[] parameters = {money,id};
                affectedRow = BaseDao.executeUpdate(conn, pstmt,sql, parameters);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            BaseDao.close(null,pstmt,null);
        }
        return affectedRow;
    }

    // 增加销量
    @Override
    public int updateSoldNumber(Connection conn, int id, int soldNumber) {
        PreparedStatement pstmt = null;
        int affectedRow = 0;
        try {
            if (conn != null) {
                // 编写sql语句,并把参数放进数组中
                String sql = "update merchant set sold_number = sold_number + ? where id = ?";
                Object[] parameters = {soldNumber,id};
                affectedRow = BaseDao.executeUpdate(conn, pstmt,sql, parameters);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            BaseDao.close(null,pstmt,null);
        }
        return affectedRow;
    }
}
